﻿-- CHI THUC THI DOAN LENH NAY KHI CHUYEN TU PHIEN BAN CU SANG PHIEN BAN MOI ~ THAY DOI CACH TINH THUE

/*
UPDATE [TabDetailBill] SET ProductNumber = 1 WHERE (ProductNumber = 0) OR (ProductNumber IS NULL)
GO
ALTER TABLE [TabDetailBill] DROP COLUMN VATasMoney
GO
ALTER TABLE [TabDetailBill] ADD VATasMoney float DEFAULT(0)
GO*/


UPDATE [TabDetailBill] SET VATasMoney = (ISNULL(ProductMoney,0)-(ISNULL(ProductNumber,1)*(ISNULL(ProductMoney,1)/(100+VAT)*100/ISNULL(ProductNumber,1))))
-- [0] THEM COT Usage vao bang TabProducts
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabProducts' and column_name = 'Usage')
BEGIN
	ALTER TABLE [TabProducts] ADD Usage nvarchar(2000)
END
GO

-- [39] THEM COT DoctorID vao bang TabTmpPrescription
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabTmpPrescription' and column_name = 'DoctorID')
BEGIN
	ALTER TABLE [TabTmpPrescription] ADD DoctorID varchar(50)
END
GO

-- [30] THAY DOI KIEU DU LIEU CUA 
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabPrescriptionDetail' and column_name = 'ProductNumberSale')
BEGIN
	ALTER TABLE [TabPrescriptionDetail] ADD ProductNumberSale float default 0
END
GO

-- [1] BAT DAU LENH TAO STORE UpdateUsageOfProduct
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'UpdateUsageOfProduct'))
BEGIN
	DROP PROC UpdateUsageOfProduct
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[UpdateUsageOfProduct]
(
	@PID	int,
	@Usage	nvarchar(2000)
)
AS
BEGIN
	UPDATE [TabProducts] SET [Usage] = @Usage WHERE [Pid] = @Pid
END
GO

-- [2] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 88)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(88,N'Thuốc - liều dùng',N'Thuốc - liều dùng',1,75)
END
GO

-- [3] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 89)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(89,N'Bệnh - Đơn thuốc',N'Bệnh - Đơn thuốc',1,76)
END
GO

-- [3.1] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 90)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(90,N'Hoá đơn Giá trị gia tăng',N'Hoá đơn Giá trị gia tăng',1,77)
END
GO

-- [3.2] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 91)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(91,N'Sổ theo dõi vệ sinh Nhà thuốc',N'Sổ theo dõi vệ sinh Nhà thuốc',1,78)
END
GO

-- [3.3] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 92)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(92,N'Sổ theo dõi hàng sắp hết hạn',N'Sổ theo dõi hàng sắp hết hạn',1,78)
END
GO

-- [3.4] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 93)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(93,N'Sổ theo dõi hàng chờ xử lý',N'Sổ theo dõi hàng chờ xử lý',1,79)
END
GO

-- [3.5] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 94)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(94,N'Sổ nhập thuốc',N'Sổ nhập thuốc',1,80)
END
GO

-- [3.6] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 95)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(95,N'Số bán thuốc theo đơn',N'Số bán thuốc theo đơn',1,81)
END
GO

-- [3.7] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 96)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(96,N'Sổ theo dõi sử dụng thuốc hướng tâm thần',N'Sổ theo dõi sử dụng thuốc hướng tâm thần',1,82)
END
GO

-- [3.8] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 97)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(97,N'Sổ theo dõi xuất nhập thuốc hướng tâm thần',N'Sổ theo dõi xuất nhập thuốc hướng tâm thần',1,83)
END
GO

-- [3.9] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 98)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(98,N'Bảng kê đơn thuốc chi tiết',N'Bảng kê đơn thuốc chi tiết',1,84)
END
GO

-- [3.10] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 99)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(99,N'Bảng kê hoá đơn bán buôn',N'Bảng kê hoá đơn bán buôn',1,85)
END
GO

-- [3.11] BO SUNG THEM QUYEN VAO HE THONG
IF NOT EXISTS (SELECT * FROM [TabPer] WHERE [PerID] = 100)
BEGIN
	INSERT INTO TabPer(PerID,PerName,PerDes,PStatus,POrder) VALUES(100,N'Bảng kê hoá đơn dịch vụ mua vào',N'Bảng kê hoá đơn dịch vụ mua vào',1,86)
END
GO

-- [4] TAO BANG TabTmpPrescription
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabTmpPrescription')
BEGIN
	CREATE TABLE [dbo].[TabTmpPrescription](
	[AutoItem] [int] IDENTITY(1,1) NOT NULL,
	[ID] [nvarchar](50),
	[DateCreate] [datetime] NULL,
	[DeseaseID] [int] NULL,
	[Note]	nvarchar(1500))
END
GO

-- [5] TAO BANG TabTmpPrescriptionDetail
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabTmpPrescriptionDetail')
BEGIN
	CREATE TABLE [dbo].[TabTmpPrescriptionDetail](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[billID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ProductID] [int] NULL,
	[ProductName] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ProductUnit] [nvarchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ProductNumber] [float] NULL,
	[ProductGuide] nvarchar(500))
END
GO

-- [6] TAO THU TUC sp_TabTmpPrescription
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'sp_TabTmpPrescription'))
BEGIN
	DROP PROC sp_TabTmpPrescription
END
GO

-- [7] TAO THU TUC sp_TabTmpPrescription
CREATE PROC [dbo].[sp_TabTmpPrescription]
(
	@ID				varchar(50),
	@TID			varchar(50) OUTPUT,
	@DateCreate		Datetime,
	@DeseaseID		int,
	@Note			nvarchar(1500),
	@doctorID		varchar(50)
)
AS
BEGIN
	IF (NOT EXISTS (SELECT * FROM [TabTmpPrescription] WHERE [ID] = @ID))
	BEGIN
		INSERT INTO [TabTmpPrescription](DateCreate,DeseaseID,Note,doctorID) VALUES(@DateCreate,@DeseaseID,@Note,@doctorID)

		DECLARE @TmpValue varchar(50)
		DECLARE @TmpAutoItem int
		SET @TmpAutoItem = (SELECT MAX(AutoItem) AS MaxID FROM [TabTmpPrescription])
		IF (@TmpAutoItem < 1000)
		SET @TmpValue =  '000'+RTRIM(LTRIM(CAST((@TmpAutoItem) AS varchar(50))))
		ELSE
		SET @TmpValue =  RTRIM(LTRIM(CAST((@TmpAutoItem) AS varchar(50))))
		
		UPDATE [TabTmpPrescription] SET ID = @TmpValue WHERE [AutoItem] = @TmpAutoItem

		SET @TID = @TmpValue
	END
	ELSE
	BEGIN
		UPDATE [TabTmpPrescription] SET
		
		[DeseaseID]		= @DeseaseID,
		[Note]			= @Note,
		[doctorID]		= @doctorID

		WHERE [ID] = @ID

		SET @TID = @ID
	END

	DELETE [TabTmpPrescriptionDetail] WHERE [billID] = @ID
END
GO

-- [8] BAT DAU LENH TAO STORE sp_TabTmpPrescriptionDetail
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'sp_TabTmpPrescriptionDetail'))
BEGIN
	DROP PROC sp_TabTmpPrescriptionDetail
END
GO

CREATE PROC [dbo].[sp_TabTmpPrescriptionDetail](@billID varchar(50), @ProductID int, @ProductName nvarchar(500), @ProductUnit nvarchar(250), @ProductNumber float, @ProductGuide nvarchar(500))
AS
BEGIN
	INSERT INTO [TabTmpPrescriptionDetail](billID, ProductID, ProductName, ProductUnit, ProductNumber, ProductGuide) VALUES(@billID, @ProductID, @ProductName, @ProductUnit, @ProductNumber, @ProductGuide)	
END
GO

-- [9] BAT DAU LENH TAO STORE sp_DelTmpPrescription
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'sp_DelTmpPrescription'))
BEGIN
	DROP PROC sp_DelTmpPrescription
END
GO

CREATE PROC [dbo].[sp_DelTmpPrescription](@ID varchar(50))
AS
BEGIN
	DELETE [TabTmpPrescriptionDetail] WHERE [billID] = @ID

	DELETE [TabTmpPrescription] WHERE [ID] = @ID
END

-- [10] SUA BANG TabTemperature THEM COT PersonAction
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabTemperature' and column_name = 'PersonAction')
BEGIN
	ALTER TABLE [TabTemperature] ADD PersonAction nvarchar(200)
END
GO

-- [11] SUA BANG TabTemperature THEM COT PersonCheck
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabTemperature' and column_name = 'PersonCheck')
BEGIN
	ALTER TABLE [TabTemperature] ADD PersonCheck nvarchar(200)
END
GO

-- [12] TAO BANG TabSideEffects1
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabSideEffects1')
BEGIN
	CREATE TABLE [dbo].[TabSideEffects1](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[dateRep] datetime,
	[patientsName] nvarchar(250),
	[Address] nvarchar(300),
	[Age]	float,
	[Gender] bit,
	[Description] nvarchar(500),
	[drugs] nvarchar(200),
	[pID] int,
	[dosage] nvarchar(300),
	[numUsage] int,
	[packet] varchar(50),
	[Expiry]	datetime,
	[provider] nvarchar(500)
)
END
GO

-- [13] BAT DAU LENH TAO STORE UpdateUsageOfProduct
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'sp_TabSideEffects1'))
BEGIN
	DROP PROC sp_TabSideEffects1
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_TabSideEffects1]
(
	@ID int,
	@dateRep datetime,
	@patientsName nvarchar(250),
	@Address nvarchar(300),
	@Age	float,
	@Gender bit,
	@Description nvarchar(500),
	@drugs nvarchar(200),
	@pID int,
	@dosage nvarchar(300),
	@numUsage int,
	@packet varchar(50),
	@Expiry	datetime,
	@provider nvarchar(500)
)
AS
BEGIN
	IF (NOT EXISTS (SELECT * FROM TabSideEffects1 WHERE [ID] = @ID))
	BEGIN
		INSERT INTO TabSideEffects1(dateRep,patientsName,Address,Age,Gender,Description,drugs,pID,dosage,numUsage,packet,Expiry,provider)
		VALUES(@dateRep,@patientsName,@Address,@Age,@Gender,@Description,@drugs,@pID,@dosage,@numUsage,@packet,@Expiry,@provider)
	END
	ELSE
	BEGIN
		UPDATE TabSideEffects1
			SET dateRep = @dateRep,
			patientsName = @patientsName,
			Address		 = @Address,
			Age			 = @Age,
			Gender		 = @Gender,
			Description	 = @Description,
			drugs		 = @drugs,
			pID			 = @pID,
			dosage		 = @dosage,
			numUsage	 = @numUsage,
			packet		 = @packet,
			Expiry		 = @Expiry,
			provider	 = @provider
		WHERE [ID]		 = @ID		
	END
END
GO


-- [14] TAO BANG TabPatients1
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabPatients1')
BEGIN
	CREATE TABLE [dbo].[TabPatients1](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[dateRep] datetime,
	[patientsName] nvarchar(250),
	[Address] nvarchar(300),
	[Age]	float,
	[Gender] bit,
	[Doctor] nvarchar(500),
	[drugs] nvarchar(200),
	[pID] int,
	[dosage] nvarchar(300),
	[numUsage] int,
	[packet] varchar(50),
	[Expiry]	datetime,
	[provider] nvarchar(500)
)
END
GO

-- [15] BAT DAU LENH TAO STORE sp_TabPatients1
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'sp_TabPatients1'))
BEGIN
	DROP PROC sp_TabPatients1
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_TabPatients1]
(
	@ID int,
	@dateRep datetime,
	@patientsName nvarchar(250),
	@Address nvarchar(300),
	@Age	float,
	@Gender bit,
	@Doctor nvarchar(500),
	@drugs nvarchar(200),
	@pID int,
	@dosage nvarchar(300),
	@numUsage int,
	@packet varchar(50),
	@Expiry	datetime,
	@provider nvarchar(500)
)
AS
BEGIN
	IF (NOT EXISTS (SELECT * FROM TabPatients1 WHERE [ID] = @ID))
	BEGIN
		INSERT INTO TabPatients1(dateRep,patientsName,Address,Age,Gender,Doctor,drugs,pID,dosage,numUsage,packet,Expiry,provider)
		VALUES(@dateRep,@patientsName,@Address,@Age,@Gender,@Doctor,@drugs,@pID,@dosage,@numUsage,@packet,@Expiry,@provider)
	END
	ELSE
	BEGIN
		UPDATE TabPatients1
			SET dateRep = @dateRep,
			patientsName = @patientsName,
			Address		 = @Address,
			Age			 = @Age,
			Gender		 = @Gender,
			Doctor		 = @Doctor,
			drugs		 = @drugs,
			pID			 = @pID,
			dosage		 = @dosage,
			numUsage	 = @numUsage,
			packet		 = @packet,
			Expiry		 = @Expiry,
			provider	 = @provider
		WHERE [ID]		 = @ID		
	END
END
GO

-- [16] TAO BANG TabBookWattingForProcess
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabBookWattingForProcess')
BEGIN
	CREATE TABLE [dbo].[TabBookWattingForProcess](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CreateDate] datetime,
	[productName] nvarchar(250),
	[productUnit] nvarchar(200),
	[Packet] nvarchar (200),
	[ProductExpiryDate] datetime,
	[Provider] nvarchar(500),
	[ProductNumber] float,
	[Reason] nvarchar (500),
	[Handling] nvarchar(300)
)
END
GO

-- [17] BAT DAU LENH TAO STORE sp_TabBookWattingForProcess
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'sp_TabBookWattingForProcess'))
BEGIN
	DROP PROC sp_TabBookWattingForProcess
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_TabBookWattingForProcess]
(
	@ID int,
	@CreateDate datetime,
	@productName nvarchar(250),
	@productUnit nvarchar(200),
	@Packet	nvarchar(200),
	@ProductExpiryDate datetime,
	@Provider nvarchar(500),
	@ProductNumber float,
	@Reason nvarchar (500),
	@Handling nvarchar(300)
)
AS
BEGIN
	IF (NOT EXISTS (SELECT * FROM TabBookWattingForProcess WHERE [ID] = @ID))
	BEGIN
		INSERT INTO TabBookWattingForProcess(CreateDate,productName,productUnit,Packet,ProductExpiryDate,Provider,ProductNumber,Reason,Handling)
		VALUES(@CreateDate,@productName,@productUnit,@Packet,@ProductExpiryDate,@Provider,@ProductNumber,@Reason,@Handling)
	END
	ELSE
	BEGIN
		UPDATE TabBookWattingForProcess
			SET CreateDate = @CreateDate,
			productName = @productName,
			productUnit	= @productUnit,
			Packet		= @Packet,
			ProductExpiryDate = @ProductExpiryDate,
			Provider		  = @Provider,
			ProductNumber	  = @ProductNumber,
			Reason			  = @Reason,
			Handling		  = @Handling
		WHERE [ID]		 = @ID		
	END
END
GO

-- [18] TAO BANG TabCleaningPharmacies
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabCleaningPharmacies')
BEGIN
	CREATE TABLE [dbo].[TabCleaningPharmacies](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[_Month] int,
	[_Year] int,
	[_Object] nvarchar(200),
	[_D1] nvarchar (50),
	[_D2] nvarchar (50),
	[_D3] nvarchar (50),
	[_D4] nvarchar (50),
	[_D5] nvarchar (50),
	[_D6] nvarchar (50),
	[_D7] nvarchar (50),
	[_D8] nvarchar (50),
	[_D9] nvarchar (50),
	[_D10] nvarchar (50),
	[_D11] nvarchar (50),
	[_D12] nvarchar (50),
	[_D13] nvarchar (50),
	[_D14] nvarchar (50),
	[_D15] nvarchar (50),
	[_D16] nvarchar (50),
	[_D17] nvarchar (50),
	[_D18] nvarchar (50),
	[_D19] nvarchar (50),
	[_D20] nvarchar (50),
	[_D21] nvarchar (50),
	[_D22] nvarchar (50),
	[_D23] nvarchar (50),
	[_D24] nvarchar (50),
	[_D25] nvarchar (50),
	[_D26] nvarchar (50),
	[_D27] nvarchar (50),
	[_D28] nvarchar (50),
	[_D29] nvarchar (50),
	[_D30] nvarchar (50),
	[_D31] nvarchar (50)
)
END
GO

-- [19] THEM COT Status vao bang TabPrescription
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabPrescription' and column_name = 'Status')
BEGIN
	ALTER TABLE [TabPrescription] ADD Status int default 0
END
GO

-- [20] Update Status Trong bang TabPrescription
UPDATE [TabPrescription] SET Status = 0 WHERE Status IS NULL
GO

-- [21] THEM COT Status vao bang TabDetailBill
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabDetailBill' and column_name = 'VATasMoney')
BEGIN
	ALTER TABLE [TabDetailBill] ADD VATasMoney float
END
GO

-- [22] THEM COT Status vao bang TabDetailBillDeleted
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabDetailBillDeleted' and column_name = 'VATasMoney')
BEGIN
	ALTER TABLE [TabDetailBillDeleted] ADD VATasMoney float
END
GO

-- [23] TAO BANG TabBillVAT
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabBillVAT')
BEGIN
	CREATE TABLE [dbo].[TabBillVAT](
	[AutoItem] [int] IDENTITY(1,1) NOT NULL,
	[ID] [nvarchar](50) NOT NULL,
	[DateCreate] [datetime] NOT NULL,
	[billID] [varchar] (50) NOT NULL,
	[CusName]	nvarchar(500),
	[ComName]	nvarchar(500),
	[CusAddress]	nvarchar(500),
	[CusAccountBank]	nvarchar(500),
	[CusPayment]	nvarchar(500),
	[CusTax]	nvarchar(500),
	[Type]		smallint
)
END
GO

-- [24] BAT DAU LENH TAO STORE sp_ActionOnTabBillVAT
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'sp_ActionOnTabBillVAT'))
BEGIN
	DROP PROC sp_ActionOnTabBillVAT
END
GO

-- [25] TAO STORE PROC sp_ActionOnTabBillVAT
CREATE PROC sp_ActionOnTabBillVAT(	
	@ID [nvarchar](50),
	@DateCreate [datetime],
	@billID [varchar] (50),
	@CusName	nvarchar(500),
	@ComName	nvarchar(500),
	@CusAddress	nvarchar(500),
	@CusAccountBank	nvarchar(500),
	@CusPayment	nvarchar(500),
	@CusTax	nvarchar(500),
	@Type		smallint
)
AS
BEGIN
	IF NOT EXISTS (SELECT * FROM TabBillVAT WHERE ID = @ID)
	BEGIN
		INSERT INTO TabBillVAT(ID,DateCreate,billID,CusName,ComName,CusAddress,CusAccountBank,CusPayment,CusTax,Type) VALUES(@ID,@DateCreate,@billID,@CusName,@ComName,@CusAddress,@CusAccountBank,@CusPayment,@CusTax,@Type)
	END
	ELSE
	BEGIN
		UPDATE TabBillVAT SET
			DateCreate	= @DateCreate,
			billID		= @billID,
			CusName		= @CusName,
			ComName		= @ComName,
			CusAddress	= @CusAddress,
			CusAccountBank	= @CusAccountBank,
			CusPayment	= @CusPayment,
			CusTax		= @CusTax,
			Type		= @Type
		WHERE ID = @ID
	END
END
GO

-- [25] TAO BANG TabProductIngedient
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabProductIngedient')
BEGIN
	CREATE TABLE [dbo].[TabProductIngedient](
	[AutoItem] [int] IDENTITY(1,1) NOT NULL,
	[PID] int ,
	[Iid] int
)
END
GO

-- [26] THEM COT Usage vao bang TabProducts
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabProducts' and column_name = 'SellWithBill')
BEGIN
	ALTER TABLE [TabProducts] ADD SellWithBill smallint
END
GO

IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabProducts' and column_name = 'FocusMind')
BEGIN
	ALTER TABLE [TabProducts] ADD FocusMind smallint
END
GO

-- [27] Update Status Trong bang TabProducts
UPDATE [TabProducts] SET SellWithBill = 0 WHERE SellWithBill IS NULL
GO

ALTER PROCEDURE [dbo].[ActionOnTabProducts]
	(
	 @PID	 			[int]		 output ,
	 @GpID 				[int]			 = 0,
	 @P_Name			[nvarchar](200)	 = null,
	 @ManID				[nvarchar](50)   = null,
	 @P_DonViTinh 		int,
	 @P_PriceBase		[float]		=	null,
	 @P_PriceRetail		[float]		=	null,
	 @P_PriceTrade		[float]		=	null,
	 @MinBalance		[int]			= 0,
	 @MaxBalance		[int]			= 0,
	 @KipLight			[smallint]		= 0,
	 @KipWarm			[smallint]		= 0,
	 @KipChildren		[smallint]		= 0,
	 @KipSaveCondition	[varchar](50),
	 @NameSearch varchar(50)			= '',
	 @Act				[smallint]		= 0,
	 @possID			[int]			= 0,
	 @possName			[nvarchar](250)	= '',
	 @DayLive			[int]		    = 0,
	 @DayNumberWarning	[int]			= 0,
	 @NumberWarning		[int]			= 0,
	 @InfoInclude		[nvarchar](500) = '',
	 @Barcode			[varchar](50),
	 @SellWithBill		[SmallInt],
	 @FocusMind			[SmallInt]
	)

AS 
IF (@Act = 0) 
BEGIN
	INSERT INTO [TabProducts] 
		 (
		 [GpID],
		 [ManID],
		 [P_Name],
		 [P_DonViTinh],
		 [P_PriceBase],
		 [P_PriceRetail],
		 [P_PriceTrade],
		 [MinBalance],
		 [MaxBalance],
		 [KipLight],
		 [KipWarm],
		 [KipChildren],
		 [KipSaveCondition],
		 [NameSearch],
		 [possID],
		 [possName],
		 [DayLive],
		 [DayNumberWarning],
		 [NumberWarning],
		 [InfoInclude],
		 [Barcode],
		 [SellWithBill],
		 [FocusMind]
		) 
	VALUES 
		(@GpID,
		 @ManID,
		 @P_Name,
		 @P_DonViTinh,
		 @P_PriceBase,
		 @P_PriceRetail,
		 @P_PriceTrade,
		 @MinBalance,
		 @MaxBalance,
		 @KipLight,
		 @KipWarm,
		 @KipChildren,
		 @KipSaveCondition,
		 @NameSearch,
		 @possID,
		 @possName,
		 @DayLive,
		 @DayNumberWarning,
		 @NumberWarning,
		 @InfoInclude,
		 @Barcode,
		 @SellWithBill,
		 @FocusMind
		)
	Set @PID =(Select Max([PID]) FROM TabProducts)
END
ELSE IF (@Act = 1)
BEGIN
	UPDATE [TabProducts] SET
		 [GpID] 			= @GpID,
		 [ManID]			= @ManID,
		 [P_Name] 			= @P_Name,
		 [P_DonViTinh] 		= @P_DonViTinh,
		 [P_PriceBase] 		= @P_PriceBase,
		 [P_PriceRetail]	= @P_PriceRetail,
		 [P_PriceTrade] 	= @P_PriceTrade,
		 [MinBalance]		= @MinBalance,
	     [MaxBalance]		= @MaxBalance,
		 [KipLight]			= @KipLight,
		 [KipWarm]			= @KipWarm,
		 [KipChildren]		= @KipChildren,
		 [KipSaveCondition] = @KipSaveCondition,
		 [NameSearch]       = @NameSearch,
		 [possID]			= @possID,
		 [possName]			= @possName,
		 [DayLive]			= @DayLive,	
		 [DayNumberWarning]	= @DayNumberWarning,
		 [NumberWarning]	= @NumberWarning,
		 [InfoInclude]		= @InfoInclude,
		 [Barcode]			= @Barcode,
		 [SellWithBill]		= @SellWithBill,
		 [FocusMind]		= @FocusMind
	WHERE [PID]		= @PID
END 
ELSE IF (@Act = 2)
BEGIN
	DELETE FROM [TabProducts] WHERE [PID] = @PID
END
GO

-- [29] Update Status Trong bang TabProducts
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uf_GetnumInput]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_GetnumInput]
GO

CREATE FUNCTION [dbo].[uf_GetnumInput](@pid int, @packet varchar(50), @stockID varchar(50))
RETURNS float 
AS
BEGIN
	DECLARE @tmpValue float
	IF (@stockID <> '0' AND @stockID <> '')
	BEGIN
		SET @tmpValue = (SELECT SUM([productNumber]/Rate) AS [numOutput] FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[PID] = @pid AND [TabDetailBill].[Packet] = @packet AND [Wid] = @stockID)
	END
	ELSE
	BEGIN
		SET @tmpValue = (SELECT SUM([productNumber]/Rate) AS [numOutput] FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('IN1','IN2','IN3') AND [TabDetailBill].[PID] = @pid AND [TabDetailBill].[Packet] = @packet)
	END	
	RETURN @tmpValue
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uf_GetnumOutput]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_GetnumOutput]
GO

CREATE FUNCTION [dbo].[uf_GetnumOutput](@pid int, @packet varchar(50), @stockID varchar(50))
RETURNS float 
AS
BEGIN
	DECLARE @tmpValue float
	IF (@stockID <> '0' AND @stockID <> '')
	BEGIN
		SET @tmpValue = (SELECT SUM([productNumber]/Rate) AS [numOutput] FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('OUT1','OUT2','OUT3','OUT4') AND [TabDetailBill].[PID] = @pid AND [TabDetailBill].[Packet] = @packet AND [Wid] = @stockID)
	END
	ELSE
	BEGIN
		SET @tmpValue = (SELECT SUM([productNumber]/Rate) AS [numOutput] FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('OUT1','OUT2','OUT3','OUT4') AND [TabDetailBill].[PID] = @pid AND [TabDetailBill].[Packet] = @packet)
	END	
	RETURN @tmpValue
END
Go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uf_GetProductBalancer]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_GetProductBalancer]
GO

CREATE FUNCTION [dbo].[uf_GetProductBalancer](@pid int) 
RETURNS INT 
AS
BEGIN
	DECLARE @tmpValue int
	SET @tmpValue = (SELECT SUM(productNumberIn-productNumberOut) FROM TabTotalBalancer WHERE pid = @pid)
	RETURN @tmpValue
END
GO

-- [30] THEM COT ProductNumberSale vao bang TabPrescriptionDetail
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabPrescriptionDetail' and column_name = 'ProductNumberSale')
BEGIN
	ALTER TABLE [TabPrescriptionDetail] ADD ProductNumberSale float default 0
END
GO

-- [31] THEM COT billID vao bang TabPrescription
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabPrescription' and column_name = 'billID')
BEGIN
	ALTER TABLE [TabPrescription] ADD billID varchar(50)
END
GO

-- Updated on 10/09/2011
-- [32] THEM COT IsCustomer vao bang TabPartner
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabPartner' and column_name = 'IsCustomer')
BEGIN
	ALTER TABLE [TabPartner] ADD IsCustomer smallint default 1
END
GO

-- [33] THEM COT IsProvider vao bang TabPartner
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabPartner' and column_name = 'IsProvider')
BEGIN
	ALTER TABLE [TabPartner] ADD IsProvider smallint default 0
END
GO

-- [34] BAT DAU LENH TAO STORE ActionOnTabPartnerAsCustomer
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'ActionOnTabPartnerAsCustomer'))
BEGIN
	DROP PROC ActionOnTabPartnerAsCustomer
END
GO

CREATE PROC ActionOnTabPartnerAsCustomer
(
	@CusID		varchar(50),
	@Name		nvarchar(250),
	@Address	nvarchar(500),
	@Phone		varchar(50),
	@Fax		varchar(50),
	@Email		varchar(50),
	@Tax		varchar(50),
	@CusGroup	varchar(50),
	@IsProvider	smallint
)
AS
BEGIN	
	IF (NOT EXISTS (SELECT * FROM TabPartner WHERE cusID = @CusID))	
	BEGIN
		INSERT INTO TabPartner(Name,Address,Phone,Fax,Email,Tax,CusGroup,IsCustomer,IsProvider) VALUES(@Name,@Address,@Phone,@Fax,@Email,@Tax,@CusGroup,1,@IsProvider)
		DECLARE @tmpID int
		SET @tmpID = (SELECT TOP 1 AutoID FROM TabPartner ORDER BY AutoID DESC)
		UPDATE TabPartner SET CusID = 'P'+RTRIM(LTRIM(CAST(@tmpID AS varchar(15)))) WHERE AutoID  = @tmpID
	END
	ELSE
	BEGIN
		UPDATE TabPartner SET
			[Name]			= @Name,
			[Address]		= @Address,
			[Phone]			= @Phone,
			[Fax]			= @Fax,
			[Email]			= @Email,
			[Tax]			= @Tax,
			[CusGroup]		= @CusGroup,
			[IsProvider]	= @IsProvider
		WHERE CusID = @CusID AND (CusID <> '' OR CusID IS NOT NULL)
	END
END
GO

UPDATE TabPartner SET IsCustomer = 1 WHERE CusID <> '' OR CusID IS NOT NULL
GO

UPDATE TabPartner SET IsProvider = 1 WHERE ProID <> '' OR ProID IS NOT NULL
GO

UPDATE TabPartner SET CusID = ProID WHERE (ProID <> '' OR ProID IS NOT NULL) AND (CusID = '' OR CusID IS NULL)
GO

-- [35] BAT DAU LENH TAO STORE ActionOnTabPartnerAsProvider
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'ActionOnTabPartnerAsProvider'))
BEGIN
	DROP PROC ActionOnTabPartnerAsProvider
END
GO

CREATE PROC ActionOnTabPartnerAsProvider
(
	@CusID		varchar(50),
	@Name		nvarchar(250),
	@Address	nvarchar(500),
	@Phone		varchar(50),
	@Fax		varchar(50),
	@Email		varchar(50),
	@Tax		varchar(50),
	@CusGroup	varchar(50),
	@Website	varchar(200),
	@IsCustomer	smallint
)
AS
BEGIN	
	IF (NOT EXISTS (SELECT * FROM TabPartner WHERE cusID = @CusID))	
	BEGIN
		INSERT INTO TabPartner(Name,Address,Phone,Fax,Email,Tax,CusGroup,Website,IsCustomer,IsProvider) VALUES(@Name,@Address,@Phone,@Fax,@Email,@Tax,@Website,@CusGroup,@IsCustomer,1)
		DECLARE @tmpID int
		SET @tmpID = (SELECT TOP 1 AutoID FROM TabPartner ORDER BY AutoID DESC)
		UPDATE TabPartner SET CusID = 'P'+RTRIM(LTRIM(CAST(@tmpID AS varchar(15)))) WHERE AutoID  = @tmpID
	END
	ELSE
	BEGIN
		UPDATE TabPartner SET
			[Name]			= @Name,
			[Address]		= @Address,
			[Phone]			= @Phone,
			[Fax]			= @Fax,
			[Email]			= @Email,
			[Tax]			= @Tax,
			[Website]		= @Website,
			[CusGroup]		= @CusGroup,
			[IsCustomer]	= @IsCustomer
		WHERE CusID = @CusID AND (CusID <> '' OR CusID IS NOT NULL)
	END
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_getMoneyAddInTime') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_getMoneyAddInTime]
GO

CREATE FUNCTION [dbo].[uf_getMoneyAddInTime]
(
	@CusID varchar(50),
	@ObjDate1 datetime,
	@ObjDate2 datetime
)
RETURNS float
AS
BEGIN
	DECLARE @tmpValue float
	SET @tmpValue = (SELECT SUM([MoneyAfterDiscount]) AS MySum FROM [TabBill] WHERE [PID] = @CusID AND [TypeInput] IN ('IN1','IN2','IN3') AND CreateDate BETWEEN @ObjDate1 AND @ObjDate2)
	RETURN @tmpValue
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_getTotalMoneyPayedProvider') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_getTotalMoneyPayedProvider]
GO

CREATE FUNCTION [dbo].[uf_getTotalMoneyPayedProvider]
(
	@CusID varchar(50),
	@ObjDate1 datetime,
	@ObjDate2 datetime
)
RETURNS float
AS
BEGIN
	DECLARE @tmpValue float
	SET @tmpValue = (SELECT SUM([TotalMoney]) AS MySum FROM [TabInOutBill] WHERE [ObjectID] = @CusID AND [TypeInput] IN ('OUT') AND [CreateDate] BETWEEN @ObjDate1 AND @ObjDate2)
	RETURN @tmpValue
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_getDebtInTime') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_getDebtInTime]
GO

CREATE FUNCTION [dbo].[uf_getDebtInTime]
(
	@CusID varchar(50),
	@ObjDate1 datetime,
	@ObjDate2 datetime
)
RETURNS float
AS
BEGIN
	DECLARE @tmpValue float
	SET @tmpValue = (SELECT SUM([MoneyAfterDiscount]) AS MySum FROM [TabBill] WHERE [PID] = @CusID AND [TypeInput] IN ('OUT1','OUT2','OUT3') AND CreateDate BETWEEN @ObjDate1 AND @ObjDate2)
	RETURN @tmpValue
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_getCollectionInTime') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_getCollectionInTime]
GO

CREATE FUNCTION [dbo].[uf_getCollectionInTime]
(
	@CusID varchar(50),
	@ObjDate1 datetime,
	@ObjDate2 datetime
)
RETURNS float
AS
BEGIN
	DECLARE @tmpValue float
	SET @tmpValue = (SELECT SUM([TotalMoney]) AS MySum FROM [TabInOutBill] WHERE [ObjectID] = @CusID AND [TypeInput] IN ('IN') AND [CreateDate] BETWEEN @ObjDate1 AND @ObjDate2)
	RETURN @tmpValue
END
GO

-----------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_getMoneyAddInTime_V0') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_getMoneyAddInTime_V0]
GO

CREATE FUNCTION [dbo].[uf_getMoneyAddInTime_V0]
(
	@CusID varchar(50),
	@ObjDate1 datetime
)
RETURNS float
AS
BEGIN
	DECLARE @tmpValue float
	SET @tmpValue = (SELECT SUM([MoneyAfterDiscount]) AS MySum FROM [TabBill] WHERE [PID] = @CusID AND [TypeInput] IN ('IN1','IN2','IN3') AND CreateDate < @ObjDate1)
	RETURN @tmpValue
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_getTotalMoneyPayedProvider_V0') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_getTotalMoneyPayedProvider_V0]
GO

CREATE FUNCTION [dbo].[uf_getTotalMoneyPayedProvider_V0]
(
	@CusID varchar(50),
	@ObjDate1 datetime
)
RETURNS float
AS
BEGIN
	DECLARE @tmpValue float
	SET @tmpValue = (SELECT SUM([TotalMoney]) AS MySum FROM [TabInOutBill] WHERE [ObjectID] = @CusID AND [TypeInput] IN ('OUT') AND [CreateDate] < @ObjDate1)
	RETURN @tmpValue
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_getDebtInTime_V0') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_getDebtInTime_V0]
GO

CREATE FUNCTION [dbo].[uf_getDebtInTime_V0]
(
	@CusID varchar(50),
	@ObjDate1 datetime
)
RETURNS float
AS
BEGIN
	DECLARE @tmpValue float
	SET @tmpValue = (SELECT SUM([MoneyAfterDiscount]) AS MySum FROM [TabBill] WHERE [PID] = @CusID AND [TypeInput] IN ('OUT1','OUT2','OUT3') AND CreateDate < @ObjDate1)
	RETURN @tmpValue
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_getCollectionInTime_V0') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_getCollectionInTime_V0]
GO

CREATE FUNCTION [dbo].[uf_getCollectionInTime_V0]
(
	@CusID varchar(50),
	@ObjDate1 datetime
)
RETURNS float
AS
BEGIN
	DECLARE @tmpValue float
	SET @tmpValue = (SELECT SUM([TotalMoney]) AS MySum FROM [TabInOutBill] WHERE [ObjectID] = @CusID AND [TypeInput] IN ('IN') AND [CreateDate] < @ObjDate1)
	RETURN @tmpValue
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_GetnumFirstBalancer') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_GetnumFirstBalancer]
GO

CREATE FUNCTION [dbo].[uf_GetnumFirstBalancer](@pid int, @stockID varchar(50), @objDate1 datetime , @objDate2 datetime)
RETURNS float 
AS
BEGIN
	DECLARE @tmpValue1 float
	DECLARE @tmpValue2 float
	DECLARE @tmpValue3 float
	IF (@stockID <> '0' AND @stockID <> '')
	BEGIN
		SET @tmpValue1 = (SELECT SUM([productNumber]) AS [numInput]  FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('IN1','IN2','IN3') AND (TabBill.CreateDate BETWEEN @objDate1 AND @objDate2) AND [TabDetailBill].[PID] = @pid AND [Wid] = @stockID)
		SET @tmpValue2 = (SELECT SUM([productNumber]) AS [numOutput] FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('OUT1','OUT2','OUT3','OUT4') AND (TabBill.CreateDate BETWEEN @objDate1 AND @objDate2) AND [TabDetailBill].[PID] = @pid AND [Wid] = @stockID)
		SET @tmpValue3 = (ISNULL(@tmpValue1,0) - ISNULL(@tmpValue2,0))
	END
	ELSE
	BEGIN
		SET @tmpValue1 = (SELECT SUM([productNumber]) AS [numInput]  FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('IN1','IN2','IN3') AND (TabBill.CreateDate BETWEEN @objDate1 AND @objDate2) AND [TabDetailBill].[PID] = @pid)
		SET @tmpValue2 = (SELECT SUM([productNumber]) AS [numOutput] FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('OUT1','OUT2','OUT3','OUT4') AND (TabBill.CreateDate BETWEEN @objDate1 AND @objDate2) AND [TabDetailBill].[PID] = @pid)
		SET @tmpValue3 = (ISNULL(@tmpValue1,0) - ISNULL(@tmpValue2,0))
	END	
	RETURN @tmpValue3
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_GetnumInputInTime') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_GetnumInputInTime]
GO

CREATE FUNCTION [dbo].[uf_GetnumInputInTime](@pid int, @stockID varchar(50), @objDate1 datetime , @objDate2 datetime)
RETURNS float 
AS
BEGIN
	DECLARE @tmpValue1 float
	IF (@stockID <> '0' AND @stockID <> '')
	BEGIN
		SET @tmpValue1 = (SELECT SUM([productNumber]) AS [numInput]  FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('IN1','IN2','IN3') AND (TabBill.CreateDate BETWEEN @objDate1 AND @objDate2) AND [TabDetailBill].[PID] = @pid AND [Wid] = @stockID)
	END
	ELSE
	BEGIN
		SET @tmpValue1 = (SELECT SUM([productNumber]) AS [numInput]  FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('IN1','IN2','IN3') AND (TabBill.CreateDate BETWEEN @objDate1 AND @objDate2) AND [TabDetailBill].[PID] = @pid)
	END	
	RETURN @tmpValue1 
END
Go

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_GetnumOutputInTime') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_GetnumOutputInTime]
GO

CREATE FUNCTION [dbo].[uf_GetnumOutputInTime](@pid int, @stockID varchar(50), @objDate1 datetime , @objDate2 datetime )
RETURNS float 
AS
BEGIN
	DECLARE @tmpValue1 float
	IF (@stockID <> '0' AND @stockID <> '')
	BEGIN
		SET @tmpValue1 = (SELECT SUM([productNumber]) AS [numInput]  FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('OUT1','OUT2','OUT3','OUT4') AND (TabBill.CreateDate BETWEEN @objDate1 AND @objDate2) AND [TabDetailBill].[PID] = @pid AND [Wid] = @stockID)
	END
	ELSE
	BEGIN
		SET @tmpValue1 = (SELECT SUM([productNumber]) AS [numInput]  FROM [TabDetailBill],[TabBill] WHERE [TabDetailBill].[billID] = [TabBill].[ID] AND [TypeInput] IN ('OUT1','OUT2','OUT3','OUT4') AND (TabBill.CreateDate BETWEEN @objDate1 AND @objDate2) AND [TabDetailBill].[PID] = @pid)
	END	
	RETURN @tmpValue1 
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].uf_GetnumMaxBalancer') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_GetnumMaxBalancer]
GO

CREATE FUNCTION [dbo].[uf_GetnumMaxBalancer](@pid int)
RETURNS float 
AS
BEGIN
	DECLARE @tmpValue1 float
	SET @tmpValue1 = (SELECT SUM([MaxBalance]) AS [MaxBalance]  FROM [TabProducts] WHERE [PID] = @pid)
	RETURN @tmpValue1 
END
GO 
-- [36] TAO BANG TabProductPlan
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabProductPlan')
BEGIN
	CREATE TABLE [dbo].[TabProductPlan](
	[AutoItem]				[int] IDENTITY(1,1) NOT NULL,
	[DateCreate]			[datetime] NULL,	
	[Note]					nvarchar(500)
	)
END
GO

-- [37] TAO BANG TabProductPlanDetail
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabProductPlanDetail')
BEGIN
	CREATE TABLE [dbo].[TabProductPlanDetail](
	[PlanID]				[int] NOT NULL,
	[ProductName]			nvarchar(200) NULL,
	[ProductUnit]			nvarchar(200),
	[numFirstBalancer]		float,
	[ProductInputInTime]	float,
	[ProductTotal]			float,
	[ProductOutputInTime]	float,
	[ProductYesNo]			float,
	[ProductBalancer]		float,
	[NumPlan]				float
	)
END
GO

-- [38] SUA TRIGGER TabDetailBill_insert_trigger
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TabDetailBill_insert_trigger] ON [dbo].[TabDetailBill]
AFTER INSERT
AS
BEGIN
 DECLARE @packet varchar(50)
 DECLARE @Pid int
 DECLARE @productPrice2 float	
 DECLARE @ProductMoney float	
 DECLARE @ProductNumber float
 DECLARE @VAT			float
 DECLARE @billID		varchar(50)  

 SET @packet		= (SELECT TOP 1 packet FROM INSERTED)
 SET @Pid			= (SELECT TOP 1 pid FROM INSERTED)
 SET @productPrice2	= (SELECT TOP 1 productPrice2 FROM INSERTED)
 SET @ProductMoney	= (SELECT TOP 1 ProductMoney FROM INSERTED)
 SET @ProductNumber	= (SELECT TOP 1 ProductNumber FROM INSERTED)
 SET @VAT	        = (SELECT TOP 1 VAT FROM INSERTED)
 SET @billID	    = (SELECT TOP 1 billID FROM INSERTED)

 IF (@productPrice2 IS NULL)
 BEGIN	
		SET @productPrice2 = (SELECT TOP 1 productPrice2 FROM [TabDetailBill] WHERE [Pid] = @Pid AND [packet] = @packet AND billID IN (SELECT ID FROM TabBill WHERE [TypeInput] IN('IN1','IN2','IN3')))		
 END
 	
 UPDATE [TabDetailBill] SET [productPrice2] = @productPrice2 WHERE billID = (SELECT TOP 1 billID FROM INSERTED) AND [pid] = @Pid AND [packet] = @packet

-- TINH VAT CHO HANG HOA
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabDetailBill' and column_name = 'VATasMoney')
BEGIN
	UPDATE [TabDetailBill] SET VATasMoney = (@ProductMoney-(@ProductNumber*(@ProductMoney/(100+@VAT)*100/ISNULL(@ProductNumber,1)))) WHERE billID = @billID
END
-- KET THUC VIEC TINH VAT CHO HANG HOA

-- BAT DAU PHAN CAP NHAT BANG HANG TON
	BEGIN
	DECLARE @TypeInput varchar(50)
	SET @TypeInput = (SELECT TOP 1 TypeInput FROM TabBill WHERE ID = (SELECT TOP 1 BillID FROM INSERTED))
	
	DECLARE @StockID varchar(50)
	SET @StockID = (SELECT TOP 1 WID FROM TabBill WHERE ID = (SELECT TOP 1 BillID FROM INSERTED))

	DECLARE @productName nvarchar(250)
	SET @productName = (SELECT TOP 1 productName FROM INSERTED)

	--SET @productNumber = (SELECT TOP 1 productNumber FROM INSERTED)	

	DECLARE @Rate float
	SET @Rate = (SELECT TOP 1 Rate FROM INSERTED)

	DECLARE @ProductDate Datetime
	SET @ProductDate = (SELECT TOP 1 ProductDate FROM INSERTED)
	
	DECLARE @ProductExpiryDate Datetime
	SET @ProductExpiryDate = (SELECT TOP 1 ProductExpiryDate FROM INSERTED)

	DECLARE @productUnit nvarchar(200)
	SET @productUnit = (SELECT TOP 1 productUnit FROM INSERTED)
	
	DECLARE @productPrice float
	SET @productPrice = (SELECT TOP 1 productPrice FROM INSERTED)

	SET @ProductMoney = (SELECT TOP 1 ProductMoney FROM INSERTED)
	
	DECLARE @NationalID varchar(50)
	SET @NationalID = (SELECT TOP 1 NationalID FROM INSERTED)
	
	-- Cap nhat bang TabTotalBalancer
	IF (@TypeInput IN ('IN1','IN2','IN3'))
	BEGIN	

		SET @productNumber = (SELECT SUM(productNumber/Rate) FROM TabDetailBill WHERE billID IN (SELECT ID FROM TabBill WHERE TypeInput IN ('IN1','IN2','IN3') AND WID = @StockID) AND [Pid] = @Pid AND Packet = @Packet)

		IF NOT EXISTS (SELECT * FROM [TabTotalBalancer] WHERE [Packet] = @Packet AND [Pid] = @Pid AND StockID = @StockID)
		BEGIN
			INSERT INTO [TabTotalBalancer](Packet,Pid,productName,productNumberIn,ProductDate,ProductExpiryDate,productNumberOut,productUnit,MinBalance,MaxBalance,productPrice,ProductMoney,NationalID,VAT,StockID) 
			
			VALUES(@Packet,@Pid,@productName,@productNumber,@ProductDate,@ProductExpiryDate,0,@productUnit,0,0,@productPrice,@ProductMoney,@NationalID,@VAT,@StockID)
		END	
		ELSE
		BEGIN
			UPDATE [TabTotalBalancer] SET [productNumberIn] = @productNumber, [productPrice] = @productPrice  WHERE [Packet] = @Packet AND [Pid] = @Pid AND StockID = @StockID
		END
	END
	ELSE
	BEGIN
		-- TINH VAT
		SET @VAT = (SELECT TOP 1 VAT FROM TabDetailBill WHERE Packet = @Packet AND Pid = @Pid AND billID IN (SELECT ID FROM TabBill WHERE TypeInput = 'IN1'))
		UPDATE [TabDetailBill] SET [VAT] = @VAT WHERE billID = (SELECT TOP 1 billID FROM INSERTED) AND [pid] = @Pid AND [packet] = @packet
		-- KET THUC TINH VAT
		SET @productNumber = (SELECT SUM(productNumber/Rate) FROM TabDetailBill WHERE billID IN (SELECT ID FROM TabBill WHERE TypeInput IN ('OUT1','OUT2','OUT3','OUT4') AND WID = @StockID) AND [Pid] = @Pid AND Packet = @Packet)
		IF NOT EXISTS (SELECT * FROM [TabTotalBalancer] WHERE [Packet] = @Packet AND [Pid] = @Pid AND StockID = @StockID)
		BEGIN
			INSERT INTO [TabTotalBalancer](Packet,Pid,productName,productNumberIn,ProductDate,ProductExpiryDate,productNumberOut,productUnit,MinBalance,MaxBalance,productPrice,ProductMoney,NationalID,VAT,StockID) VALUES(@Packet,@Pid,@productName,0,@ProductDate,@ProductExpiryDate,@productNumber,@productUnit,0,0,@productPrice,@ProductMoney,@NationalID,@VAT,@StockID)
		END	
		ELSE
		BEGIN
			UPDATE [TabTotalBalancer] SET [productNumberOut] = @productNumber, [productPrice] = @productPrice  WHERE [Packet] = @Packet AND [Pid] = @Pid AND StockID = @StockID
		END		
	END	

	UPDATE [TabTotalBalancer] SET [MinBalance] = (SELECT TOP 1 [MinBalance] FROM [TabProducts] WHERE [Pid] = @Pid), [MaxBalance] = (SELECT TOP 1 [MaxBalance] FROM [TabProducts] WHERE [Pid] = @Pid) WHERE [Packet] = @Packet AND [Pid] = @Pid
END
-- KET THUC PHAN CAP NHAT BANG HANG TON
END
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

-- [40] THEM COT ProductPrice vao bang TabPrescriptionDetail
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabPrescriptionDetail' and column_name = 'ProductPrice')
BEGIN
	ALTER TABLE [TabPrescriptionDetail] ADD ProductPrice float
END
GO

-- [41] THEM COT ProductMoney vao bang TabPrescriptionDetail
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabPrescriptionDetail' and column_name = 'ProductMoney')
BEGIN
	ALTER TABLE [TabPrescriptionDetail] ADD ProductMoney float
END
GO

UPDATE [TabPrescriptionDetail] SET ProductPrice = 0 WHERE ProductPrice IS NULL
GO

UPDATE [TabPrescriptionDetail] SET ProductMoney = 0 WHERE ProductMoney IS NULL
GO

-- [42] BAT DAU LENH TAO STORE sp_TabPrescriptionDetail
IF EXISTS (select object_definition([object_id]) as ProcedureDefinition from sys.objects where ([type] = 'P') and ([name] = 'sp_TabPrescriptionDetail'))
BEGIN
	DROP PROC sp_TabPrescriptionDetail
END
GO

CREATE PROC [dbo].[sp_TabPrescriptionDetail](@billID varchar(50), @ProductID int, @ProductName nvarchar(500), @ProductUnit nvarchar(250), @ProductNumber float, @ProductGuide nvarchar(500), @ProductPrice float, @ProductMoney float)
AS
BEGIN
	INSERT INTO [TabPrescriptionDetail](billID, ProductID, ProductName, ProductUnit, ProductNumber, ProductGuide, ProductPrice, ProductMoney) VALUES(@billID, @ProductID, @ProductName, @ProductUnit, @ProductNumber, @ProductGuide, @ProductPrice, @ProductMoney)	
END

GO

-- [43] THEM COT interaction vao bang TabProducts
IF NOT EXISTS (SELECT [column_name] FROM information_schema.columns WHERE [table_name] = 'TabProducts' and column_name = 'interaction')
BEGIN
	ALTER TABLE [TabProducts] ADD interaction nvarchar(500)
END
GO

-- [44] Update Status Trong bang TabProducts
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uf_getProviderName]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[uf_getProviderName]
GO

CREATE FUNCTION [dbo].[uf_getProviderName]
(
	@CusID varchar(50)
)
RETURNS nvarchar(250)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @tmpName nvarchar(250)

SET @tmpName = (SELECT [Name] FROM TabPartner WHERE [CusID] = @CusID AND IsProvider = 1)
	-- Return the result of the function
	RETURN @tmpName

END
GO